﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class GuiaSolicitacaoRepository : IRepository<GuiaSolicitacao>
    {
        private IDbCommand Command { get; set; }

        public GuiaSolicitacaoRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public GuiaSolicitacao GetById(string numeroGuia)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, [local], idTipoGuia, idOperadoraPrestador, dataEmissao FROM tbGuiaSolicitacao WHERE numeroGuia='" +
                    numeroGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    GuiaSolicitacao guia = new GuiaSolicitacao();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.IDTipoGuia = int.Parse(reader["idTipoGuia"].ToString());
                    guia.IDOperadoraPrestador = int.Parse(reader["idOperadoraPrestador"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());

                    return guia;
                }
                else
                {
                    throw new Exception("Guia não encontrada");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<GuiaSolicitacao> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, [local],idTipoGuia, idOperadoraPrestador,dataEmissao FROM tbGuiaSolicitacao";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<GuiaSolicitacao> list = new List<GuiaSolicitacao>();
                while (reader.Read())
                {
                    GuiaSolicitacao guia = new GuiaSolicitacao();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.IDTipoGuia = int.Parse(reader["idTipoGuia"].ToString());
                    guia.IDOperadoraPrestador = int.Parse(reader["idOperadoraPrestador"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());

                    list.Add(guia);
                }
                return list.AsQueryable<GuiaSolicitacao>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(GuiaSolicitacao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("INSERT INTO tbGuiaSolicitacao(numeroGuia, [local], idTipoGuia, idOperadoraPrestador,dataEmissao) VALUES ('{0}','{1}','{2}','{3}','{4}')", entity.NumeroGuia, entity.Local, entity.IDTipoGuia, entity.IDOperadoraPrestador, entity.DataEmissao.ToUniversalTime());
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();

                return -1;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(GuiaSolicitacao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbGuiaSolicitacao WHERE numeroGuia='" + entity.NumeroGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(GuiaSolicitacao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("UPDATE tbGuiaSolicitacao SET [local]='{1}',dataEmissao='{3}',idTipoGuia='{2}',idOperadoraPrestador='{4}' WHERE numeroGuia='{0}'",
                    entity.NumeroGuia, entity.Local, entity.IDTipoGuia, entity.DataEmissao.ToUniversalTime(),entity.IDOperadoraPrestador);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
